﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Eloquera.Client;
using _430GroupProject.Messaging;
using Model.Messaging;
using Model;
using System.Collections;

namespace _430GroupProject
{
    /// <summary>
    /// Any service that would otherwise return void returns an ErrorMessage to indicate if the operation execuated succesfully and if it did not, some details about why.
    /// Most of the other returned object contain an ErrorMessage with a similar meaning, so the state of this ErrorMessage should be checked before using the object.
    /// </summary>
    public class EMSServices : IEMSServices
    {
        private static string dbErrorString = "Database Retrieval Error.";

        /// <summary>
        /// Returns the User object corresponding to the given userID.
        /// </summary>
        /// <param name="netID">The userID of the returned user.</param>
        /// <returns>User object corresponding to the given userID.</returns>
        public User getUser(String netID)
        {
            User user = new User("NetID", "Not", "Found", "Error");
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                user = (User)db.ExecuteScalar("SELECT User WHERE NetID = " + "'" + netID + "'");
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to retrieve user from database.";

                user.Error = em;
            }
            return user;
        }

        /// <summary>
        /// Gets all the users in the DataBase. (Work in Progress)
        /// </summary>
        public Users getAllUsers()
        {
            DB db = null;
            Users accounts = new Users();
            try
            {
                db = DataBase.getConnection();
                accounts.Accounts = new List<User>();

                /*accounts.Accounts*/
                IEnumerable users = db.ExecuteQuery("SELECT User");
                foreach (User u in users)
                {
                    accounts.Accounts.Add(u);
                }

                db.Close();
            }
            catch (Exception) {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to retieve users from database.";

                accounts.Error = em;
            }

            return accounts;
        }

        /// <summary>
        /// Gets a List of the netIDs of all the users in the system.
        /// </summary>
        /// <returns>List of the netIDs of all the users in the system.</returns>
        public List<string> getAllUserNames()
        {
            DB db = null;
            List<String> usrNames = new List<String>();
            try
            {
                db = DataBase.getConnection();

                /*accounts.Accounts*/
                IEnumerable users = db.ExecuteQuery("SELECT User");
                foreach (User u in users)
                {
                    usrNames.Add(u.NetID);
                }

                db.Close();
            }
            catch (Exception) {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to get user names from database.";
            }

            return usrNames;
        }

        /// <summary>
        /// Returns the User that matches the given userName and password, or null if there is no match.
        /// </summary>
        /// <param name="userName">The userName of the User</param>
        /// <param name="password">The password of the User</param>
        /// <returns>the User that matches the given userName and password, or null if there is no match.</returns>
        public User getUserLogon(String userName, String password)
        {
            
            
            DB db = null;
            User user = null;
            try{
                db = DataBase.getConnection();
                user = (User)db.ExecuteScalar("SELECT User WHERE NetID = " + "'" + userName + "'");
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
            }

            if (user == null)
            {
                user = new User("d", "d", "d", "d");
                user.Error = new ErrorMessage();
                user.Error.Header = "Incorrect Username";
                user.Error.Message = "We're unable to find your Username";
            }

            else if (user.Password != password)
            {
                user.Error = new ErrorMessage();
                user.Error.Header = "Incorrect Password";
                user.Error.Message = "Please enter a new password and try again.";
            }

            return user;
        }

        /// <summary>
        /// Updates the password for the given user to the given password.
        /// </summary>
        /// <param name="userName">The user whos has changes his/her password</param>
        /// <param name="newPassword">The new password</param>
        public ErrorMessage updatePassword(String userID, String newPassword)
        {
            return AdminServices.updateUserPassword(userID, newPassword);
        }

        /// <summary>
        /// Updates the user with any changes values inside the user object.
        /// This should be used anytime something on a user changes.
        /// </summary>
        /// <param name="user">The user with changes properties.</param>
        public ErrorMessage updateUser(User user)
        {
            return AdminServices.updateUser(user).Error;
        }

        /// <summary>
        /// Adds a new work session.
        /// This should be used with a user manually creates and enters a completeled work session.
        /// Note: don't use this for updating a clockinsession.
        /// </summary>
        /// <param name="ws"></param>
        public ErrorMessage addWorkSession(WorkSession ws)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(ws);
                db.Close();
            }catch(Exception){
                checkDB(db);
                ErrorMessage em= new ErrorMessage();
                em.Header = dbErrorString;
                em.Header = "Unable to add work session.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Updates the worksession with new values.
        /// This should be called after a user clocks in.
        /// </summary>
        /// <param name="ws">The changed worksession object.</param>
        public ErrorMessage updateWorkSession(WorkSession ws)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(ws);
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Header = "Unable to update work session.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Returns all the work groups that the given user is a supervisor for.
        /// </summary>
        /// <param name="userID">The user that is a supervisor of the returned groups.</param>
        /// <returns>WorkGroups that the user is a supervisor for.</returns>
        public WorkGroups SupervisorFor(String userID)
        {
            WorkGroups WGs = new WorkGroups();
            DB db = null;
            User user = null;
            try
            {
                db = DataBase.getConnection();
                user = (User)db.ExecuteScalar("SELECT User WHERE UserId = " + "'" + userID  + "'");
                db.Close();
            }
            catch (Exception) {
                checkDB(db);
                WGs.Error = new ErrorMessage();
                WGs.Error.Header = dbErrorString;
                WGs.Error.Message = "Unable to retrieve supervisors from database.";
                return WGs;
            }

            List<String> groupNames = user.Manages;
            List<WorkGroup> groups = getGroupsFromNames(groupNames);
            WGs.Groups = groups;
            return WGs;
        }

        /// <summary>
        /// Returns all the work groups that the given user is an employee of.
        /// </summary>
        /// <param name="userID">The user that is an employee of the return groups.</param>
        /// <returns>WorkGroups that the user is an employee of</returns>
        public WorkGroups EmployeeOf(String userID)
        {
            DB db = null;

            WorkGroups WGs = new WorkGroups();
            User user = null;
            try
            {
                db = DataBase.getConnection();
                user = (User)db.ExecuteScalar("SELECT User WHERE UserId = " + "'" + userID + "'");
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                WGs.Error = new ErrorMessage();
                WGs.Error.Header = dbErrorString;
                WGs.Error.Message = "Unable to retrieve employees from database.";
                return WGs;
            }
            List<String> groupNames = user.WorksIn;
            List<WorkGroup> groups = getGroupsFromNames(groupNames);
            WGs.Groups = groups;
            return WGs;
        }


        /// <summary>
        /// Returns the work sessions for a user between the start and end dates (Inclusive).
        /// </summary>
        /// <param name="netID">The user who worked in the sessions.</param>
        /// <param name="start">The beginning of the work period.</param>
        /// <param name="end">The end of the work period.</param>
        /// <returns></returns>
        public WorkSessions userWorkSessions(String netID, DateTime start, DateTime end)
        {
            DB db = null;
            WorkSessions ret = new WorkSessions();
            try
            {
                db = DataBase.getConnection();
                IEnumerable sessions = db.ExecuteQuery("SELECT WorkSession WHERE UserID = '" + netID + "' AND PayStart = '" + start + "' AND PayEnd = '" + end + "'");
                ret.Sessions = new List<WorkSession>();
            
                foreach (WorkSession i in sessions)
                {
                    if (!ret.Sessions.Contains(i))
                    {
                        ret.Sessions.Add(i);
                    }
                }
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ret.Error = new ErrorMessage();
                ret.Error.Header = dbErrorString;
                ret.Error.Message = "Unable to retrieve work sessions from database.";
                return ret;
            }
            
            return ret;
        }

        /// <summary>
        /// Returns the work group object that corresponds to the given name.
        /// </summary>
        /// <param name="workGroupName"></param>
        /// <returns></returns>
        public WorkGroup getWorkGroup(String workGroupName)
        {
            DB db = null;
            WorkGroup ret = new WorkGroup();
            try
            {
                db = DataBase.getConnection();
                ret = (WorkGroup)db.ExecuteScalar("SELECT WorkGroup WHERE Name = '" + workGroupName + "'");

                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ret.Error = new ErrorMessage();
                ret.Error.Header = dbErrorString;
                ret.Error.Message = "Unable to retrieve work groups from database.";
                return ret;
            }
            return ret;
        }

        /// <summary>
        /// Updates the given workgroup with changed values.
        /// </summary>
        /// <param name="toUpdate">The workgroup with changed values.</param>
        /// <returns>The workgroup that was updated</returns>
        public WorkGroup updateWorkGroup(WorkGroup toUpdate)
        {
            DB db = null;
            WorkGroup ret = new WorkGroup();
            try
            {
                db = DataBase.getConnection();

                ret = (WorkGroup)db.ExecuteScalar("SELECT WorkGroup WHERE Name = '" + toUpdate.Name + "'");
                //ret.PayrollContact = toUpdate.PayrollContact; TO DO: make payroll contact public
                ret.Schedules = toUpdate.Schedules;
                //ret.Supervisors = toUpdate.Supervisors;
                ret.SupervisorID = toUpdate.SupervisorID;
                //ret.Employees = toUpdate.Employees; TO DO: see above
                db.Store(ret);
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ret.Error = new ErrorMessage();
                ret.Error.Header = dbErrorString;
                ret.Error.Message = "Unable to retrieve work groups from database.";
                return ret;
            }
            return ret;

        }

        /// <summary>
        /// gets all the workGroups registered, for admin use
        /// </summary>
        /// <returns> all the workGroups registered</returns>
        public WorkGroups getAllWorkgroups()
        {
            DB db = null;
            WorkGroups groups = new WorkGroups();
            try{
                db = DataBase.getConnection();
            
                groups.Groups = new List<WorkGroup>();
                IEnumerable wgs = db.ExecuteQuery("SELECT WorkGroup");
                foreach (WorkGroup group in wgs)
                {
                    groups.Groups.Add(group);
                }
            }
            catch (Exception)
            {
                checkDB(db);
                groups.Error = new ErrorMessage();
                groups.Error.Header = dbErrorString;
                groups.Error.Message = "Unable to retrieve work groups from database.";
            }

            return groups;

        }

        /// <summary>
        /// Returns the schedule that corresponds to the given name.
        /// </summary>
        /// <param name="scheduleName">The name of the schedule to be returned.</param>
        /// <returns>Schedule that corresponds to the given name.</returns>
        public Schedule getSchedule(String scheduleName)
        {
            DB db = null;
            Schedule ret = new Schedule();
            try
            {
                db = DataBase.getConnection();
                ret = (Schedule)db.ExecuteScalar("SELECT Schedule WHERE Name = '" + scheduleName + "'");

                db.Close();
            }
            catch (Exception) {
                checkDB(db);
                ret.Error = new ErrorMessage();
                ret.Error.Header = dbErrorString;
                ret.Error.Message = "Unable to retrieve work groups from database.";
            }
            return ret;
        }

        /// <summary>
        /// Updates the given schedule in the database with any changes on the object.
        /// </summary>
        /// <param name="changedSchedule">Schedule that has changed.</param>
        public ErrorMessage updateSchedule(Schedule changedSchedule)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(changedSchedule);
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to add user to data base.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Adds the new schedule to the database.
        /// </summary>
        /// <param name="newSchedule">New Schedule to be added to the database.</param>
        public ErrorMessage addSchedule(Schedule newSchedule)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(newSchedule);
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to add user to data base.";
                return em;
            }
            return null;
        }

        public Users getUsersInWorkGroup(string workGroupName)
        {
            DB db = null;
            Users ret = new Users();
            try
            {
                db = DataBase.getConnection();

                ret.Accounts = new List<User>();
                IEnumerable members = db.ExecuteQuery("SELECT User"); //TO DO: It looks like eloqura only uses arrays... find cleaner workaround
                foreach (User user in members)
                {
                    if (user.WorksIn.Contains(workGroupName))
                    {
                        ret.Accounts.Add(user);
                    }
                }
            }
            catch (Exception) {
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to retrieve users from the database.";
                ret.Error = em;
            }
            return ret;
        }

        /// <summary>
        /// Gets a list of Workgroups for a list of work group names.
        /// </summary>
        /// <param name="groupNames">List of workgroup names</param>
        /// <returns></returns>
        private List<WorkGroup> getGroupsFromNames(List<String> groupNames)
        {
            DB db = null;
            List<WorkGroup> groups = new List<WorkGroup>();
            try
            {
                db = DataBase.getConnection();
                
                foreach (String name in groupNames)
                {
                    groups.Add((WorkGroup)db.ExecuteScalar("SELECT WorkeGroup WHERE Name = " + "'" + name + "'"));
                }
                db.Close();
            }
            catch(Exception) {
                checkDB(db);
            }
            return groups;
        }

        /// <summary>
        /// Retrieves a list of all the workgroup names in the system.
        /// </summary>
        /// <returns>list of all the workgroup names in the system.</returns>
        public List<String> getAllWorkgroupNames()
        {
            DB db = null;
            List<String> wgNames = new List<String>();
            try
            {
                db = DataBase.getConnection();

                /*accounts.Accounts*/
                IEnumerable groups = db.ExecuteQuery("SELECT WorkGroup");
                foreach (WorkGroup wg in groups)
                {
                    wgNames.Add(wg.Name);
                }

                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to get user names from database.";
            }

            return wgNames;
        }

        /// <summary>
        /// Adds the new user to the database.
        /// </summary>
        /// <param name="user">new user to be added to the database.</param>
        public ErrorMessage addUser(User user)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(user);
                db.Close();
            }
            catch(Exception) {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to add user to data base.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Adds a new workGroup to the database.
        /// </summary>
        /// <param name="newGroup">Adds a new workgroup to the database.</param>
        public ErrorMessage addWorkGroup(WorkGroup newGroup)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(newGroup);
                db.Close();
            }
            catch (Exception) {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to add work group to data base.";
                return em;
            }
            return null;
        }

        #region messaging

        /// <summary>
        /// Sends the given message to the message.to users.
        /// </summary>
        /// <param name="message">The message to be sent.</param>
        public ErrorMessage sendMessage(Message message)
        {
            return MessageService.sendMessage(message);
        }

        /// <summary>
        /// Retrieves all message from the given user.
        /// </summary>
        /// <param name="userID">The user to sent the messages.</param>
        /// <returns>A list of messages that the given user has sent.</returns>
        public Messages getMessagesFrom(string userID)
        {
            return MessageService.getMessagesFrom(userID);
        }

        /// <summary>
        /// Retrieves all messages to the given user.
        /// </summary>
        /// <param name="userID">The user to recived the messages.</param>
        /// <returns>A list of messages that the given user has recieved.</returns>
        public Messages getMessageTo(string userID)
        {
            return MessageService.getMessageTo(userID);
        }

        /// <summary>
        /// Updates a message in the database when its contents have been changed.
        /// For example if a user reads a message can marks it as read, the message will need to be updated in the DB.
        /// </summary>
        /// <param name="message">The message whos contents have changed.</param>
        public ErrorMessage updateMessage(Message message)
        {
            return MessageService.updateMessage(message);   
        }
        #endregion

        /// <summary>
        /// Adds a new timesheet to the database.
        /// </summary>
        /// <param name="TS">A new timesheet.</param>
        public ErrorMessage addTimeSheet(TimeSheet TS)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();

                db.Store(TS);
                db.Close();
            }
            catch (Exception) {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Couldn't Add timesheet to database.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Retrieves all Open (Not approved by payroll) times
        /// </summary>
        /// <param name="usr">User who sumitted the timesheets.</param>
        public TimeSheets getOpenTimeSheets(User usr)
        {
            TimeSheets ret = new TimeSheets();
            DB db = null;
            try
            {
                db = DataBase.getConnection();

                var timeSheets = db.ExecuteQuery("SELECT TimeSheet");

                db.Close();
                List<TimeSheet> list = new List<TimeSheet>();
                foreach (TimeSheet ts in timeSheets)
                {
                    if (ts.User.NetID.Equals(usr.NetID) && !ts.isApprovedByPayroll())
                    {
                        list.Add(ts);
                    }
                }
                ret.Sheets = list;
            }
            catch (Exception) {
                checkDB(db);

                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Coudn't retieve time sheets.";
                ret.Error = em;
            }

            return ret;
        }

        /// <summary>
        /// Updates the Timesheet with changed values in the database.
        /// </summary>
        /// <param name="TS">The timesheet with changes values.</param>
        public ErrorMessage updateTimeSheet(TimeSheet TS)
        {
            DB db = null;
            try
            {
                db = DataBase.getConnection();
                db.Store(TS);
                db.Close();
            }
            catch (Exception) {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Unable to update time sheet in database.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Marks the timesheet as approved by supervisor.
        /// </summary>
        /// <param name="TS">The timesheet to be marked as approved.</param>
        /// <returns>True if the timesheet was updated sucessfully, false otherwise.</returns>
        public bool markApprovedBySupervisor(TimeSheet TS)
        {
            try
            {
                TS.supervisorApprove();
                updateTimeSheet(TS);
            }
            catch (Exception) {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Marks the timesheet as approved by payroll.
        /// </summary>
        /// <param name="TS">The timesheet to be marked as approved.</param>
        /// <returns>True if the timesheet was updated sucessfully, false otherwise.</returns>
        public bool markApprovedByPayroll(TimeSheet TS)
        {
            try
            {
                TS.payrollApprove();
                updateTimeSheet(TS);
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Marks the given timesheet as submitted and ready for approval by supervisor.
        /// </summary>
        /// <param name="TS">The timesheet to be submitted</param>
        public ErrorMessage submitTimeSheet(TimeSheet TS)
        {
            return TS.submit();
        }

        /// <summary>
        /// Gets the timeslot with the given IDs from the database.
        /// </summary>
        /// <param name="timeID">The id of the timeslot to retrieve.</param>
        /// <returns>The timeslot corresponding to the given timeslot id.</returns>
        public TimeSlot getTimeSlot(Guid timeSName)
        {
            DB db = null;
            TimeSlot ts = new TimeSlot();
            try
            {
                db = DataBase.getConnection();
                ts = (TimeSlot)db.ExecuteScalar("SELECT TimeSlot WHERE TimeSlotID = '" + timeSName + "'");
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Couldn't retrive timesheet to database.";
                ts.Error = em;
                return ts;
            }
            return ts;
        }

        /// <summary>
        /// Updates an exsisting Timesheet with the given timesheet object.
        /// </summary>
        /// <param name="updateTs">A changed timesheet to be updated.</param>
        /// <returns>null if no errors occur, or an ErrorMessage containing details about why the operation failed otherwise.</returns>
        public ErrorMessage upDateTimeSlot(TimeSlot updateTs)

        {
            DB db = null;
            TimeSlot ts = new TimeSlot();
            try
            {
                db = DataBase.getConnection();
                ts = (TimeSlot)db.ExecuteScalar("SELECT TimeSlot WHERE TimeSlotID = '" + updateTs.TimeSlotID + "'");
                ts.AssignedUsers = updateTs.AssignedUsers;
                ts.AssignedUsersList = updateTs.AssignedUsersList;
                db.Store(ts);
                db.Close();
            }
            catch (Exception)
            {
                checkDB(db);
                ErrorMessage em = new ErrorMessage();
                em.Header = dbErrorString;
                em.Message = "Couldn't update timesheet to database.";
                return em;
            }
            return null;
        }

        /// <summary>
        /// Checks if the given DB object is Open, if so it will be closed.
        /// </summary>
        /// <param name="db">The DB object that may be open.</param>
        private void checkDB(DB db)
        {
            if (db != null && db.IsOpen)
                db.Close();
        }
    }
}
